﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;

namespace PMSWenLvTing.DAL
{
    public class DBHelper
    {
        private string connStr;
        private MySqlConnection conn;
        private MySqlCommand cmd;
        private MySqlDataAdapter adapt;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DBHelper()
        {
            connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        }
        #region 混合事务
        /// <summary>
        /// 混合事务执行增加、修改、删除
        /// </summary>
        /// <param name="sqlList">语句List</param>
        /// <param name="typeList">语句类型List</param>
        /// <param name="paramList">参数List</param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQueryWithTransaction(List<string> sqlList, List<CommandType> typeList, List<MySqlParameter[]> paramList)
        {

            using (conn = new MySqlConnection(connStr))
            {
                int result = 0;
                MySqlTransaction trans = null;
                try
                {
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandType = typeList[i];//语句类型
                        cmd.CommandText = sqlList[i];// 语句名称
                        if (paramList[i] != null)
                        {
                            cmd.Parameters.AddRange(paramList[i]);//遍历参数
                        }
                        result += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit(); //提交事务
                    conn.Close();
                }
                catch(Exception ex)
                {
                    trans.Rollback();//回滚事务
                    result = -1;
                    conn.Close();
                }
                return result;
            }

        }
        #endregion

        #region 混合事务(要求影响行数)
        /// <summary>
        /// 混合事务执行增加、修改、删除(要求影响行数)
        /// </summary>
        /// <param name="sqlList">语句List</param>
        /// <param name="typeList">语句类型List</param>
        /// <param name="paramList">参数List</param>
        /// <param name="requestCount">要求影响行数</param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQueryWithTransaction(List<string> sqlList, List<CommandType> typeList, List<MySqlParameter[]> paramList, int requestCount)
        {

            using (conn = new MySqlConnection(connStr))
            {
                int result = 0;
                MySqlTransaction trans = null;
                try
                {
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandType = typeList[i];//语句类型
                        cmd.CommandText = sqlList[i];// 语句名称
                        if (paramList[i] != null)
                        {
                            cmd.Parameters.AddRange(paramList[i]);//遍历参数
                        }
                        result += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    if (result == requestCount)
                    {
                        //符合要求影响的行数
                        trans.Commit(); //提交事务
                        conn.Close();
                    }
                    else
                    {
                        //不符合要求影响的行数
                        trans.Rollback();//回滚事务
                        conn.Close();
                        result = -1;
                    }
                }
                catch(Exception ex)
                {
                    trans.Rollback();//回滚事务
                    conn.Close();
                    result = -1;
                }
                return result;
            }

        }


        /// <summary>
        /// 执行需要返回参数的特殊事务
        /// </summary>
        /// <param name="sqlList">语句List</param>
        /// <param name="typeList">语句类型List</param>
        /// <param name="paramList">参数List</param>
        /// <param name="getParamList">是否执行首行首列List</param>
        /// <param name="needParamList">是否接收首行首列参数List</param>
        /// <param name="paramNameList">接收字段名称List</param>
        /// <returns></returns>
        public int ExecuteSpecialTransaction(List<string> sqlList, List<CommandType> typeList, List<MySqlParameter[]> paramList, List<bool> getParamList, List<bool> needParamList, List<string> paramNameList)
        {
            using (conn = new MySqlConnection(connStr))
            {
                int result = 0;
                object getParam = null;//接收返回的首行首列参数
                MySqlTransaction trans = null;
                try
                {
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandType = typeList[i];//语句类型
                        cmd.CommandText = sqlList[i];// 语句名称

                        //需要返回的首行首列作为参数
                        if (paramList[i] != null && needParamList[i])
                        {
                            //将返回的首行首列赋值给第一个参数
                            MySqlParameter[] param = paramList[i];
                            param[0] = new MySqlParameter(paramNameList[i], getParam);//替换参数
                            cmd.Parameters.AddRange(param);//遍历参数
                        }
                        else if (paramList[i] != null)
                        {
                            cmd.Parameters.AddRange(paramList[i]);//遍历参数
                        }

                        //判断是否执行返回首行首列语句
                        if (getParamList[i])
                        {
                            getParam = cmd.ExecuteScalar();//返回首行首列
                            result = result + 1;
                        }
                        else
                        {
                            result += cmd.ExecuteNonQuery();
                        }

                        cmd.Parameters.Clear();
                    }
                    trans.Commit(); //提交事务
                    conn.Close();
                }
                catch(Exception ex)
                {
                    trans.Rollback();//回滚事务
                    result = -1;
                    conn.Close();
                }
                return result;
            }
        }

        #endregion

        #region SQL语句



        /// <summary>
        /// 执行增加、修改、删除
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string sql, CommandType cmdType, MySqlParameter[] param)
        {

            using (conn = new MySqlConnection(connStr))
            {
                int result = 0;
                try
                {
                    conn.Open();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    cmd.CommandText = sql;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    result = cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch(Exception ex)
                {
                    conn.Close();
                    result = -1;
                }
                return result;
            }

        }



        /// <summary>
        /// 执行查询表格
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>表格</returns>
        public DataTable GetDataTable(string sql, CommandType cmdType, MySqlParameter[] param)
        {

            using (conn = new MySqlConnection(connStr))
            {
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    cmd.CommandText = sql;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    adapt = new MySqlDataAdapter(cmd);
                    adapt.Fill(ds);
                    dt = ds.Tables[0];
                    conn.Close();
                }
                catch(Exception ex)
                {
                    conn.Close();
                    dt = null;
                }
                return dt;
            }

        }

        /// <summary>
        /// 执行查询表格组
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>表格组</returns>
        public DataSet GetDataSet(string sql, CommandType cmdType, MySqlParameter[] param)
        {

            using (conn = new MySqlConnection(connStr))
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    cmd.CommandText = sql;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    adapt = new MySqlDataAdapter(cmd);
                    adapt.Fill(ds);
                    conn.Close();
                }
                catch(Exception ex)
                {
                    ds = null;
                    conn.Close();
                }
                return ds;
            }

        }


        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>首行首列</returns>
        public object ExecuteScalar(string sql, CommandType cmdType, MySqlParameter[] param)
        {
            using (conn = new MySqlConnection(connStr))
            {
                object obj = new object();
                try
                {
                    conn.Open();
                    cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    cmd.CommandText = sql;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    obj = cmd.ExecuteScalar();
                    conn.Close();
                }
                catch(Exception ex)
                {
                    obj = null;
                    conn.Close();
                }
                return obj;
            }
        }

        #endregion

        #region 测试服务器的连通性
        /// <summary>
        /// 测试连接服务器
        /// </summary>
        /// <returns>测试结果</returns>
        public bool ConnectServer()
        {
            using (conn = new MySqlConnection(connStr))
            {
                bool result = false;
                try
                {
                    conn.Open();
                    result = true;
                }
                catch
                {
                    result = false;
                }
                conn.Close();
                return result;
            }

        }
        #endregion
    }
}
